package com.xoa.dev.pz.service;

import com.xoa.dao.common.SysCodeMapper;

import com.xoa.model.common.SysCode;

import com.xoa.model.users.Users;
import com.xoa.dev.pz.dao.MenuJdtMapper;
import com.xoa.dev.pz.dao.PzPersonFlowMapper;
import com.xoa.dev.pz.dao.PzPersonMapper;
import com.xoa.dev.pz.model.PzPersonFlow;
import com.xoa.dev.pz.model.PzPersonWithBLOBs;
import com.xoa.service.users.UsersService;
import com.xoa.dao.party.GridInfoMapper;
import com.xoa.model.party.GridInfo;
import com.xoa.util.DateFormat;
import com.xoa.util.ExcelUtil;
import com.xoa.util.ToJson;
import com.xoa.util.common.StringUtils;
import com.xoa.util.common.log.FileUtils;
import com.xoa.util.common.session.SessionUtils;
import com.xoa.util.netdisk.CheckAll;
import com.xoa.util.page.PageParams;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by gsb on 2017/12/4.
 */
@Service
public class PzPersonFlowService {

    @Resource
    private PzPersonFlowMapper pzPersonFlowMapper;

    @Resource
    private PzPersonMapper pzPersonMapper;

    @Resource
    private UsersService usersService;

    @Resource
    public MenuJdtMapper menuJdtMapper;

    @Resource
    private SysCodeMapper sysCodeMapper;

    @Resource
    private GridInfoMapper gridInfoMapper;

    /**
     * 创建作者:   季佳伟
     * 创建日期:   2017年12月04日 下午15:08:00
     * 方法介绍:   流动人口添加
     * 参数说明:
     * 返回值说明:
     */
    public ToJson<PzPersonFlow> insertSelective(PzPersonFlow pzPersonFlow){
        ToJson<PzPersonFlow> json = new ToJson<>();
        try {
            int count = pzPersonFlowMapper.insertSelective(pzPersonFlow);

            if(count > 0){
                json.setFlag(0);
                json.setMsg("true");
            }
        }catch (Exception e){
            e.printStackTrace();
            json.setMsg(e.getMessage());
            json.setFlag(1);
        }
        return json;
    }
    /**
     * 创建作者:   季佳伟
     * 创建日期:   2017年12月04日 下午15:08:00
     * 方法介绍:   流动人口修改
     * 参数说明:
     * 返回值说明:
     */
    public ToJson<PzPersonFlow> updateByPrimaryKeySelective(PzPersonFlow pzPersonFlow, String specialStr){
        ToJson<PzPersonFlow> json = new ToJson<>();
        try {
            pzPersonFlow.setRemark1(DateFormat.getDatestr(new Date()));
            int count=pzPersonFlowMapper.updateByPrimaryKeySelective(pzPersonFlow);
            PzPersonWithBLOBs pzPerson=new PzPersonWithBLOBs();
            pzPerson.setpId(Integer.valueOf(pzPersonFlow.getPersonId()));
            pzPerson.setSpecialStr(specialStr);
            count+=pzPersonMapper.upSpecialStrByPId(pzPerson);
            json.setFlag(0);
            json.setMsg("true");
        }catch (Exception e){
            e.printStackTrace();
            json.setMsg(e.getMessage());
            json.setFlag(1);
        }
        return json;

    }
    /**
     * 创建作者:   季佳伟
     * 创建日期:   2017年12月04日 下午15:08:00
     * 方法介绍:   流动人口删除
     * 参数说明:
     * 返回值说明:
     */
    public ToJson<PzPersonFlow> deleteByPrimaryKey(int personId){
        ToJson<PzPersonFlow> json = new ToJson<>();
        try {
            pzPersonFlowMapper.deleteByPrimaryKey(personId);
            json.setFlag(0);
            json.setMsg("true");
        }catch (Exception e){
            e.printStackTrace();
            json.setMsg(e.getMessage());
            json.setFlag(1);
        }
        return json;
    }
    /**
     * 创建作者:   季佳伟
     * 创建日期:   2017年12月04日 下午15:08:00
     * 方法介绍:   流动人口详情
     * 参数说明:
     * 返回值说明:
     */
    public ToJson<PzPersonFlow> queryById(int personId){
        ToJson<PzPersonFlow> json = new ToJson<>();
        try {
            PzPersonFlow pzPersonFlow = pzPersonFlowMapper.queryById(personId);
            json.setObject(pzPersonFlow);
            json.setFlag(0);
            json.setMsg("true");
        }catch (Exception e){
            e.printStackTrace();
            json.setMsg(e.getMessage());
            json.setFlag(1);
        }
        return json;
    }
    /**
     * 创建作者:   季佳伟
     * 创建日期:   2017年12月04日 下午15:08:00
     * 方法介绍:   流动人口列表
     * 参数说明:
     * 返回值说明:
     */
    public ToJson<PzPersonFlow> queryAll(PzPersonFlow pzPersonFlow, Integer page, Integer pageSize, boolean useFlag, HttpServletRequest request, HttpServletResponse response, String export){
        ToJson<PzPersonFlow> json = new ToJson<>();
        try {
            PageParams pageParams=new PageParams();
            pageParams.setPage(page);
            pageParams.setPageSize(pageSize);
            pageParams.setUseFlag(useFlag);
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("page", pageParams);

            Map<String, Object> param = new HashMap<String, Object>();
            Users user= SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
            param.put("userId", user.getUserId());
            param.put("userPriv", user.getUserPriv());
            param.put("deptId", user.getDeptId());
            String menuModel=menuJdtMapper.selMenuByModel("1");
            if(!CheckAll.checkAll(menuModel,param)){
                pzPersonFlow.setCreateUser(user.getUserId());
            }
            map.put("pzPersonFlow",pzPersonFlow);
            if(export==null){
                export="0";
            }
            List<PzPersonFlow> list = pzPersonFlowMapper.queryAll(map);
            SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
            for(PzPersonFlow flow : list){
               if(!StringUtils.checkNull(flow.getGridUserId())){
                   flow.setUserNames(usersService.getUserNameById(flow.getGridUserId()));
               }
                if ("0".equals(flow.getPersonSex())) {
                    flow.setpSexStr("男");
                } else {
                    flow.setpSexStr("女");
                }
                if(flow.getpDate()!=null){
                    flow.setpDateStr(sd.format(flow.getpDate()));
                }
                SysCode sysCode = sysCodeMapper.getSingleCode("POLITICAL", flow.getpPolitical());
                if (sysCode != null) {
                    flow.setpPoliticalStr(sysCode.getCodeName());
                }
                SysCode sysCode1 = sysCodeMapper.getSingleCode("DEGREE", flow.getpEducation());
                if (sysCode1 != null) {
                    flow.setpEducationStr(sysCode1.getCodeName());
                }
                if ("01".equals(flow.getpMaritalStatus())) {
                    flow.setpMaritalStatusStr("未婚");
                } else {
                    flow.setpMaritalStatusStr("已婚");
                }
                if ("0".equals(flow.getpVehicles())) {
                    flow.setpVehiclesStr("无");
                } else {
                    flow.setpVehiclesStr("有");
                }

                if("1".equals(flow.getInFlowCause())){
                    flow.setInFlowCauseStr("务工");
                } else  if("2".equals(flow.getInFlowCause())){
                    flow.setInFlowCauseStr("务农");
                } else  if("3".equals(flow.getInFlowCause())){
                    flow.setInFlowCauseStr("经商");
                } else  if("4".equals(flow.getInFlowCause())){
                    flow.setInFlowCauseStr("学习培训");
                } else  if("5".equals(flow.getInFlowCause())){
                    flow.setInFlowCauseStr("婚嫁");
                } else  if("6".equals(flow.getInFlowCause())){
                    flow.setInFlowCauseStr("随迁");
                } else  if("7".equals(flow.getInFlowCause())){
                    flow.setInFlowCauseStr("治病疗养");
                } else  if("8".equals(flow.getInFlowCause())){
                    flow.setInFlowCauseStr("投靠亲友");
                } else  if("9".equals(flow.getInFlowCause())){
                    flow.setInFlowCauseStr("工作调动");
                } else  if("10".equals(flow.getInFlowCause())){
                    flow.setInFlowCauseStr("其他");
                }
                if(flow.getEconomicSource()!=null) {
                    flow.setEconomicSourceStr(flow.getEconomicSource().equals("1") ? "有" : "无");
                }else{
                    flow.setEconomicSourceStr("无");
                }
                if("1".equals(flow.getResidenceType())){
                    flow.setResidenceTypeStr("自购房屋");
                } else  if("2".equals(flow.getResidenceType())){
                    flow.setResidenceTypeStr("租赁房屋");
                } else  if("3".equals(flow.getResidenceType())){
                    flow.setResidenceTypeStr("寄宿");
                } else  if("4".equals(flow.getResidenceType())){
                    flow.setResidenceTypeStr("借住");
                } else  if("5".equals(flow.getResidenceType())){
                    flow.setResidenceTypeStr("单位宿舍");
                } else  if("6".equals(flow.getResidenceType())){
                    flow.setResidenceTypeStr("单位宿舍");
                } else  if("7".equals(flow.getResidenceType())){
                    flow.setResidenceTypeStr("农民工公寓");
                } else  if("8".equals(flow.getResidenceType())){
                    flow.setResidenceTypeStr("其他");
                }

                if("1".equals(flow.getRegistrationType())){
                    flow.setRegistrationTypeStr("登记类");
                } else  if("2".equals(flow.getRegistrationType())){
                    flow.setRegistrationTypeStr("临时居住证");
                } else  if("3".equals(flow.getRegistrationType())){
                    flow.setRegistrationTypeStr("居住证");
                }

                if("1".equals(flow.getLivingTime())){
                    flow.setLivingTimeStr("一个月");
                } else  if("2".equals(flow.getLivingTime())){
                    flow.setLivingTimeStr("一个月至三个月");
                } else  if("3".equals(flow.getLivingTime())){
                    flow.setLivingTimeStr("三个月至半年");
                } else  if("4".equals(flow.getLivingTime())){
                    flow.setLivingTimeStr("半年至一年");
                } else  if("5".equals(flow.getLivingTime())){
                    flow.setLivingTimeStr("一年以上");
                } else  if("6".equals(flow.getLivingTime())){
                    flow.setLivingTimeStr("俩年以上");
                }
                if(flow.getExaminationProve()!=null){
                    flow.setExaminationProveStr(flow.getExaminationProve().equals("1")?"是":"否");
                }else{
                    flow.setExaminationProveStr("否");
                }
                if(flow.getCompilancePolicy()!=null){
                    flow.setCompilancePolicyStr(flow.getCompilancePolicy().equals("1")?"是":"否");
                }else{
                    flow.setCompilancePolicyStr("否");
                }
                if(flow.getChildEmploy()!=null){
                    flow.setChildEmployStr(flow.getChildEmploy().equals("1")?"是":"否");
                }else{
                    flow.setChildEmployStr("否");
                }
                if(flow.getChildSchool()!=null){
                    flow.setChildSchoolStr(flow.getChildSchool().equals("1")?"是":"否");
                }else{
                    flow.setChildSchoolStr("否");
                }
                if(flow.getSocialSecurity()!=null){
                    flow.setSocialSecurityStr(flow.getSocialSecurity().equals("1")?"是":"否");
                }else{
                    flow.setSocialSecurityStr("否");
                }
                if(flow.getHealthInsurance()!=null){
                    flow.setHealthInsuranceStr(flow.getHealthInsurance().equals("1")?"是":"否");
                }else{
                    flow.setHealthInsuranceStr("否");
                }
            }
            json.setObj(list);
            json.setTotleNum(pageParams.getTotal());
            json.setFlag(0);
            json.setMsg("true");

            if(export.equals("1")){
                HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead("流动人口信息导出", 32);
                String[] secondTitles = {"所属网格","姓名","性别","身份证", "曾用名/别名", "出生日期","民族","联系方式","政治面貌","文化程度","婚姻状况","宗教信仰","职业","工作单位","户籍地址","籍贯","户籍地详址","现居地址","名下机动车辆","车牌号","备注","流入原因", "流入时间", "经济来源", "住所类型", "办证类型","证件号码","登记日期","证件到期日期","已居住时限","预计居住时间","是否有婚育证明","子女数","是否符合政策","子女是否就业","子女是否上学","是否缴纳社保","是否缴纳医保","居住证发放情况","积分情况","备注"};
                HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook1, secondTitles);
                String[] beanProperty = {"gridName","personName","pSexStr","personCradId","pAlias", "pDateStr","pNation","pPhone","pPoliticalStr","pEducationStr","pMaritalStatusStr","pReligion","pOccupation","pWorkUnit","pDomicile","pOrigin","pDetailedDomicile","pAddress","pVehiclesStr","str1","pRemarks", "inFlowCauseStr", "inFlowTime", "economicSourceStr", "residenceTypeStr", "registrationTypeStr", "idNum","registrationTime","certificateTime","livingTimeStr","expectLiveTime","examinationProveStr","childrenNum","compilancePolicyStr","childEmployStr","childSchoolStr","socialSecurityStr","healthInsuranceStr","residencePermitCondition","integralSituation","remark"};
                HSSFWorkbook workbook = null;
                workbook = ExcelUtil.exportExcelData(workbook2,list, beanProperty);
                OutputStream out = response.getOutputStream();
                String filename = "流动人口信息记录表.xls";
                filename = FileUtils.encodeDownloadFilename(filename, request.getHeader("user-agent"));
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.setHeader("content-disposition", "attachment;filename=" + filename);
                workbook.write(out);
                out.close();
            }
        }catch (Exception e){
            e.printStackTrace();
            json.setMsg(e.getMessage());
            json.setFlag(1);
        }
        return json;

    }
    /**
     * 创建作者:   季佳伟
     * 创建日期:   2017年12月05日 上午11:22:00
     * 方法介绍:   导入流动人口
     * 参数说明:
     * 返回值说明:
     */
    public ToJson<PzPersonFlow> importPzPersonFlow(MultipartFile file, HttpServletRequest request, HttpServletResponse response){
        ToJson<PzPersonFlow> json = new ToJson<>();
        Users users= SessionUtils.getSessionInfo(request.getSession(),Users.class,new Users());
        // 成功次数
        Integer successCount = 0;
        try {
            //判读当前系统
            //读取配置文件
            ResourceBundle rb = ResourceBundle.getBundle("upload");
            String osName = System.getProperty("os.name");
            StringBuffer path = new StringBuffer();
            if (osName.toLowerCase().startsWith("win")) {
                path = path.append(rb.getString("upload.win"));
            } else {
                path = path.append(rb.getString("upload.linux"));
            }
            // 判断是否为空文件
            if (file.isEmpty()) {
                json.setMsg("请上传文件！");
                json.setFlag(1);
                return json;
            } else {
                String fileName = file.getOriginalFilename();
                if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                    String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                    int pos = fileName.indexOf(".");
                    String extName = fileName.substring(pos);
                    String newFileName = uuid + extName;
                    File dest = new File(path.toString(), newFileName);
                    file.transferTo(dest);
                    // 将文件上传成功后进行读取文件
                    // 进行读取的路径
                    String readPath = path.append(System.getProperty("file.separator")).append(newFileName).toString();
                    InputStream in = new FileInputStream(readPath);
                    HSSFWorkbook excelObj = new HSSFWorkbook(in);
                    HSSFSheet sheetObj = excelObj.getSheetAt(0);
                    Row row = sheetObj.getRow(0);
                    int colNum = row.getPhysicalNumberOfCells();
                    int lastRowNum = sheetObj.getLastRowNum();
                    PzPersonFlow pzPersonFlow = null;
                    PzPersonWithBLOBs pzPersonWithBLOBs = null;
                    SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
                    int count = 0;

                    for (int i = 1; i < lastRowNum; i++) {
                        pzPersonFlow = new PzPersonFlow();
                        pzPersonWithBLOBs = new PzPersonWithBLOBs();
                        row = sheetObj.getRow(i);
                        String value = "";
                        if (row != null) {
                            for (int j = 0; j < colNum; j++) {
                                Cell cell = row.getCell(j);
                                if (cell != null) {
                                    switch (j) {
                                        case 0:
                                            //所属网格
                                            String as=cell.getStringCellValue().trim();
                                            GridInfo info=gridInfoMapper.selectByUGridName(as);
                                            pzPersonWithBLOBs.setpGrid(info.getGridId());
                                            break;
                                        case 1:
                                            // 姓名
                                            pzPersonWithBLOBs.setpName(cell.getStringCellValue());
                                            break;
                                        case 2:
                                            // 身份证
                                            pzPersonWithBLOBs.setpCradId(cell.getStringCellValue());
                                            break;
                                        case 3:
                                            // 别名
                                            pzPersonWithBLOBs.setpAlias(cell.getStringCellValue());
                                            break;
                                        case 4:
                                            // 性别
                                            if("男".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpSex("0");
                                            }else if("女".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpSex("1");
                                            }
                                            break;
                                        case 5:
                                            // 出生日期
                                            pzPersonWithBLOBs.setpDate(sd.parse(cell.getStringCellValue().trim()));
                                            break;
                                        case 6:
                                            // 民族
                                            pzPersonWithBLOBs.setpNation(cell.getStringCellValue());
                                            break;
                                        case 7:
                                            // 联系方式
                                            if (cell.getCellType() == CellType.NUMERIC) {
                                                BigDecimal bigP=new BigDecimal(cell.getNumericCellValue());
                                                value = bigP.toString();
                                                //解决1234.0  去掉后面的.0
                                                if(null!=value&&!"".equals(value.trim())){
                                                    String[] item = value.split("[.]");
                                                    if(1<item.length&&"0".equals(item[1])){
                                                        value=item[0];
                                                    }
                                                }
                                                pzPersonWithBLOBs.setpPhone(value);
                                            }else {
                                                pzPersonWithBLOBs.setpPhone(cell.getStringCellValue());
                                            }
                                            break;
                                        case 8:
                                            // 政治面貌
                                            pzPersonWithBLOBs.setpPolitical((sysCodeMapper.getByName("POLITICAL",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 9:
                                            // 文化程度
                                            pzPersonWithBLOBs.setpEducation((sysCodeMapper.getByName("DEGREE",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 10:
                                            // 婚姻状况
                                            if("未婚".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpMaritalStatus("01");
                                            }else if("已婚".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpMaritalStatus("02");
                                            }
                                            break;
                                        case 11:
                                            //宗教信仰
                                            pzPersonWithBLOBs.setpReligion(cell.getStringCellValue());
                                            break;
                                        case 12:
                                            //职业
                                            pzPersonWithBLOBs.setpOccupation(cell.getStringCellValue());
                                            break;
                                        case 13:
                                            //工作单位
                                            pzPersonWithBLOBs.setpWorkUnit(cell.getStringCellValue());
                                            break;
                                        case 14:
                                            //户籍地址
                                            pzPersonWithBLOBs.setpDomicile(cell.getStringCellValue());
                                            break;
                                        case 15:
                                            //籍贯
                                            pzPersonWithBLOBs.setpOrigin(cell.getStringCellValue());
                                            break;
                                        case 16:
                                            //户籍地详址
                                            pzPersonWithBLOBs.setpDetailedDomicile(cell.getStringCellValue());
                                            break;
                                        case 17:
                                            //名下机动车辆
                                            if("无".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpVehicles("0");
                                            }else if("有".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpVehicles("1");
                                            }
                                            break;
                                        case 18:
                                            //备注
                                            pzPersonWithBLOBs.setpRemarks(cell.getStringCellValue());
                                            break;
                                        case 19:
                                            //有无固定住所
                                            if("无".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpIsHouse("0");
                                            }else if("有".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setpIsHouse("1");
                                            }
                                            break;
                                        case 20:
                                            //现居地址
                                            pzPersonWithBLOBs.setpAddress(cell.getStringCellValue());
                                            break;
                                        case 21:
                                            //特殊人员标记
                                            String specialStr = cell.getStringCellValue().trim();
                                            String str = "";
                                            if(specialStr.contains("刑满释放人员")){
                                                str += "1,";
                                            }
                                            if(specialStr.contains("社区矫正人员")){
                                                str += "2,";
                                            }
                                            if(specialStr.contains("重点信访人员")){
                                                str += "3,";
                                            }
                                            if(specialStr.contains("易肇事肇祸严重精神障碍患者")){
                                                str += "4,";
                                            }
                                            if(specialStr.contains("吸毒人员")){
                                                str += "5,";
                                            }
                                            if(specialStr.contains("重点青少年")){
                                                str += "6,";
                                            }
                                            pzPersonWithBLOBs.setSpecialStr(str);
                                            break;
                                        case 22:
                                            //实口类型
                                            if("流动人口".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setFlag1("2");
                                            }else if("户籍人口".equals(cell.getStringCellValue().trim())){
                                                pzPersonWithBLOBs.setFlag1("1");
                                            }
                                            break;
                                        case 23:
                                            //车牌号
                                            cell.setCellType(CellType.STRING);
                                            pzPersonWithBLOBs.setStr1(cell.getStringCellValue());
                                            break;
                                        case 24:
                                            // 流入原因
                                            if("务工".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setInFlowCause("1");
                                            } else  if("务农".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setInFlowCause("");
                                            } else  if("经商".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setInFlowCause("3");
                                            } else  if("学习培训".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setInFlowCause("4");
                                            } else  if("婚嫁".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setInFlowCause("5");
                                            } else  if("随迁".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setInFlowCause("6");
                                            } else  if("治病疗养".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setInFlowCause("7");
                                            } else  if("投靠亲友".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setInFlowCause("8");
                                            } else  if("工作调动".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setInFlowCause("9");
                                            } else  if("其他".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setInFlowCause("10");
                                            }
                                            break;
                                        case 25:
                                            // 流入时间
                                            cell.setCellType(CellType.STRING);
                                            pzPersonFlow.setInFlowTime(cell.getStringCellValue().trim());
                                            break;
                                        case 26:
                                            //经济来源
                                            pzPersonFlow.setEconomicSource(cell.getStringCellValue().trim().equals("有")?"1":"0");
                                            break;
                                        case 27:
                                            // 住所类型
                                            if("直购房屋".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setResidenceType("1");
                                            } else  if("租赁房屋".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setResidenceType("2");
                                            } else  if("寄宿".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setResidenceType("3");
                                            } else  if("借住".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setResidenceType("4");
                                            } else  if("单位宿舍".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setResidenceType("5");
                                            } else  if("单位宿舍".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setResidenceType("6");
                                            } else  if("农民工公寓".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setResidenceType("7");
                                            } else  if("其他".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setResidenceType("8");
                                            }
                                            break;
                                        case 28:
                                            // 办证类型
                                            if("登记类".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setRegistrationType("1");
                                            } else  if("临时居住证".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setRegistrationType("2");
                                            } else  if("居住证".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setRegistrationType("3");
                                            }
                                            break;
                                        case 29:
                                            // 证件号码
                                            cell.setCellType(CellType.STRING);
                                            pzPersonFlow.setIdNum(cell.getStringCellValue());
                                            break;
                                        case 30:
                                            // 登记日期
                                            cell.setCellType(CellType.STRING);
                                            pzPersonFlow.setRegistrationTime(cell.getStringCellValue());
                                            break;
                                        case 31:
                                            // 证件到期日期
                                            cell.setCellType(CellType.STRING);
                                            pzPersonFlow.setCertificateTime(cell.getStringCellValue());
                                            break;
                                        case 32:
                                            // 已居住时限
                                            if("一个月".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setLivingTime("1");
                                            } else  if("一个月至三个月".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setLivingTime("2");
                                            } else  if("三个月至半年".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setLivingTime("3");
                                            } else  if("半年至一年".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setLivingTime("4");
                                            } else  if("一年以上".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setLivingTime("5");
                                            } else  if("俩年以上".equals(cell.getStringCellValue().trim())){
                                                pzPersonFlow.setLivingTime("6");
                                            }
                                            break;
                                        case 33:
                                            // 预计居住时间
                                            cell.setCellType(CellType.STRING);
                                            pzPersonFlow.setExpectLiveTime(cell.getStringCellValue());
                                            break;
                                        case 34:
                                            //否有婚育证明
                                            pzPersonFlow.setExaminationProve(cell.getStringCellValue().trim().equals("是")?"1":"0");
                                            break;
                                        case 35:
                                            //子女数
                                            cell.setCellType(CellType.STRING);
                                            pzPersonFlow.setChildrenNum(Integer.valueOf(cell.getStringCellValue()));
                                            break;
                                        case 36:
                                            //是否符合政策
                                            pzPersonFlow.setCompilancePolicy(cell.getStringCellValue().trim().equals("是")?"1":"0");
                                            break;
                                        case 37:
                                            //子女是否就业
                                            pzPersonFlow.setChildEmploy(cell.getStringCellValue().trim().equals("是")?"1":"0");
                                            break;
                                        case 38:
                                            //子女是否上学
                                            pzPersonFlow.setChildSchool(cell.getStringCellValue().trim().equals("是")?"1":"0");
                                            break;
                                        case 39:
                                            //是否缴纳社保
                                            pzPersonFlow.setSocialSecurity(cell.getStringCellValue().trim().equals("是")?"1":"0");
                                            break;
                                        case 40:
                                            //是否缴纳医保
                                            pzPersonFlow.setHealthInsurance(cell.getStringCellValue().trim().equals("是")?"1":"0");
                                            break;
                                        case 41:
                                            //居住证发放情况
                                            pzPersonFlow.setResidencePermitCondition(cell.getStringCellValue());
                                            break;
                                        case 42:
                                            //积分情况
                                            pzPersonFlow.setIntegralSituation(cell.getStringCellValue());
                                            break;
                                        default:
                                            json.setFlag(0);
                                            json.setMsg("err");
                                            break;
                                    }
                                }

                            }
                            List<String> nullArray=new ArrayList();
                            if(pzPersonWithBLOBs.getpGrid()==null){
                                nullArray.add("特殊网格");
                            }
                            if(pzPersonWithBLOBs.getpName()==null){
                                nullArray.add("姓名");
                            }
                            if(pzPersonWithBLOBs.getpSex()==null){
                                nullArray.add("性别");
                            }
                            if(pzPersonWithBLOBs.getpCradId()==null){
                                nullArray.add("身份证号");
                            }
                            if(pzPersonWithBLOBs.getFlag1()==null){
                                nullArray.add("实口类型");
                            }
                            if(pzPersonWithBLOBs.getpOrigin()==null){
                                nullArray.add("籍贯");
                            }
                            if(pzPersonWithBLOBs.getpIsHouse()==null){
                                nullArray.add("有无固定住所");
                            }
                            if(pzPersonWithBLOBs.getpAddress()==null){
                                nullArray.add("现居地址");
                            }
                            if(pzPersonWithBLOBs.getpMaritalStatus()==null){
                                nullArray.add("婚姻状况");
                            }
                            if(pzPersonWithBLOBs.getpDomicile()==null){
                                nullArray.add("户籍地址");
                            }
                            if(pzPersonWithBLOBs.getpDetailedDomicile()==null){
                                nullArray.add("户籍地详情");
                            }
                            if(pzPersonWithBLOBs.getpVehicles()==null){
                                nullArray.add("名下机动车辆");
                            }
                            if(pzPersonFlow.getInFlowCause()==null){
                                nullArray.add("流入原因");
                            }
                            if(pzPersonFlow.getResidenceType()==null){
                                nullArray.add("住所类型");
                            }
                            if(nullArray.size()>0){
                                json.setObject(nullArray);
                                json.setMsg("必填字段不能为空，请重新导入");
                            }else {
                                //判断用户是否为空，不为空判断用户是否存在
                                if (!StringUtils.checkNull(pzPersonWithBLOBs.getpName()) && !StringUtils.checkNull(pzPersonWithBLOBs.getpCradId())) {
                                    Map<String, Object> map = new HashMap<String, Object>();
                                    map.put("pCradId", pzPersonWithBLOBs.getpCradId());
                                    map.put("pName", pzPersonWithBLOBs.getpName());
                                    PzPersonWithBLOBs pzPersons = pzPersonMapper.selectPersonCountByNameCrad(map);
                                    if (pzPersons.getCount() > 0) {
                                        pzPersonWithBLOBs.setFlag2("0");
                                        pzPersonMapper.updateByPrimaryKeySelective(pzPersonWithBLOBs);
                                    } else {
                                        //创建用户和创建时间
                                        pzPersonWithBLOBs.setCreateUser(users.getUserId());
                                        pzPersonWithBLOBs.setCreateTime(DateFormat.getCurrentTime());
                                        pzPersonWithBLOBs.setFlag2("0");
                                        pzPersonMapper.insertSelective(pzPersonWithBLOBs);
                                    }
                                }
                                //将数据放入数据库
                                //用户表与表关联用户userid
                                pzPersonFlow.setPersonId(String.valueOf(pzPersonWithBLOBs.getpId()));
                                //判断关联用户userid是否为空,为空新增,不为空修改
                                if(!StringUtils.checkNull(String.valueOf(pzPersonFlow.getPersonId()))){
                                    //如果不为空，查询是否有信息，有则更新，否则新增
                                    Map<String, Object> map = new HashMap<String, Object>();
                                    map.put("userId", pzPersonFlow.getPersonId());
                                    PzPersonFlow pzFlow=pzPersonFlowMapper.selectInfoBypUserId(map);
                                    if(pzFlow.getCou()>0){
                                        pzPersonFlow.setPersonId(pzFlow.getPersonId());
                                        pzPersonFlow.setRemark1(DateFormat.getCurrentTime());
                                        count +=pzPersonFlowMapper.updateByPrimaryKeySelective(pzPersonFlow);
                                    }else {
                                        pzPersonFlow.setPersonId(String.valueOf(pzPersonWithBLOBs.getpId()));
                                        pzPersonFlow.setRemark1(DateFormat.getCurrentTime());
                                        count += pzPersonFlowMapper.insertSelective(pzPersonFlow);

                                    }
                                }
                            }
                        }
                    }
                    if (count > 0) {
                        successCount = successCount + count;
                    }
                    dest.delete();
                    if(successCount>0){
                        json.setFlag(0);
                        json.setMsg("ok");
                        json.setTotleNum(successCount);
                        return  json;
                    }
                }
            }

        }catch (Exception e){
            e.printStackTrace();
            json.setMsg(e.getMessage());
            json.setFlag(1);
        }
        return json;

    }
    /**
     * 创建作者:   季佳伟
     * 创建日期:   2017年12月05日 下午15:22:00
     * 方法介绍:   导出流动人口或条件查询
     * 参数说明:
     * 返回值说明:
     */
    public ToJson<PzPersonFlow> getPersonFlowExport(HttpServletRequest request, HttpServletResponse response, PzPersonFlow pzPersonFlow, String export){
        ToJson<PzPersonFlow> json = new ToJson<>();
        try {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("pzPersonFlow",pzPersonFlow);
            List<PzPersonFlow> pzPersonFlowList = pzPersonFlowMapper.queryPersonFlowList(map);
            if("0".equals(export)){
                json.setObj(pzPersonFlowList);
                json.setFlag(0);
                json.setMsg("true");
            }else if ("1".equals(export)){
                HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead("流动人口信息导出",8);
                String[] secondTitles = {"姓名", "所属网格", "性别", "流入原因", "流入时间", "经济来源", "住所类型", "办证类型"
                        , "证件号码", "登记日期", "证件到期日期", "已居住时限", "预计居住时间", "是否有婚育证明", "子女数"
                        , "是否符合政策", "子女是否就业", "子女是否上学", "是否缴纳社保", "是否缴纳医保", "居住证发放情况"
                        , "积分情况", "修改时间"};
                HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook1, secondTitles);
                String[] beanProperty = {"personName", "gridName", "personSex", "inFlowCause", "inFlowTime"
                        , "economicSource", "residenceType", "registrationType", "idNum", "registrationTime"
                        , "certificateTime", "livingTime", "expectLiveTime", "examinationProve"
                        , "childrenNum", "compilancePolicy", "childEmploy", "childSchool", "socialSecurity"
                        , "healthInsurance", "residencePermitCondition", "integralSituation", "remark"
                };
                HSSFWorkbook workbook = null;
                workbook = ExcelUtil.exportExcelData(workbook2, pzPersonFlowList, beanProperty);
                OutputStream out = response.getOutputStream();
                String filename = "流动人口信息表.xls";
                filename = FileUtils.encodeDownloadFilename(filename, request.getHeader("user-agent"));
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.setHeader("content-disposition", "attachment;filename=" + filename);
                workbook.write(out);
                out.close();



            }


        }catch (Exception e){
            e.printStackTrace();
            json.setMsg(e.getMessage());
            json.setFlag(1);
        }
        return json;

    }



    }
